/**
 * 
 */
package com.orange.modules.dao;


import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.modules.entity.AuthItem;
import com.orange.modules.entity.expert.AuthExportGroupMap;
import com.orange.modules.entity.expert.AuthGroup;
import com.orange.modules.entity.school.AuthSchool;
import com.orange.modules.entity.school.AuthSchoolGroupMap;
import com.orange.dbhelp.page.Page;
import com.orange.modules.bean.ExpertGroupBean;


/**
 * @author gaogs
 *
 * 2016-11-16
 */
@Repository
public class AuthExportGroupMapRepository {

	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	@Autowired
	private AuthExportGroupMapDao authExportGroupMapDao;
	
	
	
	/**
	 * 根据组与专家关系id查询 专家身份信息
	 * @param pid
	 * @return
	 * @throws SQLException
	 */
	public AuthExportGroupMap queryAuthExportGroupMapById(String pid) throws SQLException{
		AuthExportGroupMap ls = authExportGroupMapDao.findOne(pid);
		return ls;
	}
	/**
	 * 添加专家
	 */
	public void addAuthExportGroupMapInfo(AuthExportGroupMap a){
		authExportGroupMapDao.save(a);
	}
	
	/**
	 * 批量添加专家组员
	 * @param list
	 */
	public void addAuthExportGroupMapList(List<AuthExportGroupMap> list){
		authExportGroupMapDao.save(list);
	}
	
	//保存身份
	public void saveAuthExportGroupMap(AuthExportGroupMap a) throws SQLException{
		authExportGroupMapDao.save(a);
	}
	/**
	 * 查询专家组信息
	 */
	public Page<ExpertGroupBean> queryAuthExportGroupMapInfo(Integer pageNumber,Integer pageSize , Map<String ,String> map) throws SQLException{
		StringBuilder sql = new StringBuilder("select TOP 1000 a.p_id as pid,b.s_expert_name as expertname,"
				+ " b.s_expert_code as expertcode ,c.s_name as name, a.s_identity as identit"
				+ " FROM t_auth_export_group_map as a"
				+ " LEFT JOIN t_auth_expert as b ON a.s_export_id=b.p_id"
				+ " LEFT JOIN t_auth_group as c ON a.s_group_id=c.p_id"
				+ " WHERE 1=1");
		
		Page<ExpertGroupBean> page = dbHelper.getPage(sql.toString(), ExpertGroupBean.class, pageNumber, pageSize,new Object[] {});
		return page;
	}
	
	/**
	 * 分配关系
	 */
	public List<AuthExportGroupMap> queryAuthExportGroupIdentity() throws SQLException{
		String sql = "select a.s_identity as identit"
				+ " from t_auth_export_group_map as a where 1 = 1";
		List<AuthExportGroupMap> list = dbHelper.getBeanList(sql, AuthExportGroupMap.class);
		return list;
	}
	/**
	 * 查询专家id
	 */
	public List<AuthExportGroupMap> queryAuthExportGroupExportId() throws SQLException{
		String sql = "select a.s_export_id as exportId"
				+ " from t_auth_export_group_map as a where 1 = 1";
		List<AuthExportGroupMap> list = dbHelper.getBeanList(sql, AuthExportGroupMap.class);
		return list;
	}
	
	/**
	 * 根据组id 查询所有绑定的 专家
	 * @param expertId
	 * @return
	 * @throws SQLException 
	 */
	public List<AuthExportGroupMap> queryAuthExportGroupMapByGroupId(String groupId) throws SQLException{
		
		String sql="SELECT a.p_id as pid ,a.s_type as type ,a.s_identity as identitys,a.s_export_id as exportId ," +
				" a.s_group_id as groupId ,a.s_create_time as createTime ,b.s_expert_name as expertname " +
				" FROM t_auth_export_group_map as a " +
				" LEFT JOIN t_auth_expert as b ON a.s_export_id=b.p_id " +
				" WHERE a.s_group_id=? ";
		List<AuthExportGroupMap> list = dbHelper.getBeanList(sql, AuthExportGroupMap.class,groupId);
		return list;
	}
	
	
	/**
	 * 根据组id 分页查询所有绑定的 专家
	 * @param pageNumber
	 * @param pageSize
	 * @param condition
	 * @return
	 * @throws SQLException
	 */
	public Page<AuthExportGroupMap> queryAuthExportGroupMapPage(Map<String,String>map) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT TOP 1000 a.p_id as pid ,a.s_type as type ," +
				" a.s_identity as identitys,a.s_export_id as exportId ," +
				" a.s_group_id as groupId ,a.s_create_time as createTime ,b.s_expert_name as expertName ,b.s_expert_code as expertCode " +
				" FROM t_auth_export_group_map as a  " +
				" LEFT JOIN t_auth_expert as b ON a.s_export_id =b.p_id" +
				" WHERE a.s_group_id = "+map.get("groupId"));
		sql.append(" ORDER BY a.s_identity DESC");
		Page<AuthExportGroupMap> authExportGroupMaps = dbHelper.getPage(sql.toString(), AuthExportGroupMap.class,
				Integer.valueOf(map.get("pageNumber")), Integer.valueOf(map.get("pageSize")),new Object[] {});
		return authExportGroupMaps;
	}
	
	/**
	 * 根据组id 及身份 查询组员记录
	 * @param groupId
	 * @param identity
	 * @return
	 * @throws SQLException 
	 */
	public AuthExportGroupMap queryAuthExportGroupMapByIdentity(String groupId,String identity) throws SQLException{
		String sql="SELECT a.p_id as pid ,a.s_type as type ," +
				" a.s_identity as identitys,a.s_export_id as exportId ," +
				" a.s_group_id as groupId ,a.s_create_time as createTime  " +
				" FROM t_auth_export_group_map as a  WHERE a.s_group_id =? AND a.s_identity =?" ;
		AuthExportGroupMap a=dbHelper.getBean(sql, AuthExportGroupMap.class, groupId,identity);
		return a;
	}
	
	/**
	 * 根据id 删除专家组与组员关系记录
	 * @param pid
	 */
	public void deleteAuthExportGroupMapById(String pid){
		authExportGroupMapDao.delete(pid);
	}
	
	/**
	 * 根据现场考察专家id查询 现场考察专家身份
	 * @param expertId
	 * @return
	 * @throws SQLException 
	 */
	public AuthExportGroupMap queryAuthExportGroupMapByExpertId(String expertId) throws SQLException{
		String sql="SELECT TOP 1 a.p_id as pid ,a.s_type as type ," +
				" a.s_identity as identitys,a.s_export_id as exportId ," +
				" a.s_group_id as groupId ,a.s_create_time as createTime  " +
				" FROM t_auth_export_group_map as a " +
				" LEFT JOIN t_auth_group as b ON a.s_group_id=b.p_id  " +
				" LEFT JOIN t_auth_school as c ON b.s_school_id=c.p_id " +
				" WHERE  b.s_type = '1' AND a.s_export_id= ? and b.s_state != '3'";
		
		AuthExportGroupMap a=dbHelper.getBean(sql, AuthExportGroupMap.class,expertId);
		return a;
		
	}
	
	
	/**
	 * 材料评审专家id查询 材料评审专家身份
	 * @param expertId
	 * @return
	 * @throws SQLException 
	 */
	public AuthExportGroupMap queryAuthExportGroupMapByExpertId1(String expertId) throws SQLException{
		String sql="SELECT a.p_id as pid ,a.s_type as type ," +
				" a.s_identity as identitys,a.s_export_id as exportId ," +
				" a.s_group_id as groupId ,a.s_create_time as createTime  " +
				" FROM t_auth_export_group_map as a " +
				" LEFT JOIN t_auth_group as b ON a.s_group_id=b.p_id  " +
				" WHERE  b.s_type = '0' AND a.s_export_id=? ";
		AuthExportGroupMap a=dbHelper.getBean(sql, AuthExportGroupMap.class,expertId);
		return a;
		
	}
	/**
	 * 材料评审专家组-根据专家id查询专家与组的关系
	 * @param groupId
	 * @param identity
	 * @return
	 * @throws SQLException 
	 */
	public AuthExportGroupMap queryAuthExportGroupMapByExpertId_material(String expertId) throws SQLException{
		String sql="SELECT a.p_id as pid ,a.s_type as type ," +
				" a.s_identity as identitys,a.s_export_id as exportId ," +
				" a.s_group_id as groupId ,a.s_create_time as createTime  " +
				" FROM t_auth_export_group_map as a  WHERE a.s_type='0' AND  a.s_export_id =? " ;
		AuthExportGroupMap a=dbHelper.getBean(sql, AuthExportGroupMap.class,expertId);
		return a;
	}
	/**
	 * 根据组id查询专家组的详细信息
	 */
	public Page<AuthGroup> queryAuthGroupInfoByPage(Map<String,String>map) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT TOP 1000 a.p_id as pid ,a.s_type as type ," +
				" a.s_name as name ,a.s_state as state ,a.s_school_id as schoolId ," +
				" a.s_update_time as updateTime ,a.s_create_time as createTime ," +
				" a.s_report_name as reportName ,a.s_report_url as reportUrl ,b.s_school_name as schoolName ,b.s_major as major" +
				" FROM t_auth_group as a LEFT JOIN t_auth_school as b ON a.s_school_id=b.p_id" +
				" WHERE a.p_id = "+map.get("groupId")) ;
		Page<AuthGroup> authGroup = dbHelper.getPage(sql.toString(), AuthGroup.class,
				Integer.valueOf(map.get("pageNumber")), Integer.valueOf(map.get("pageSize")),new Object[] {});
		return authGroup;
	}
	/**
	 * 根据组的状态查询组id
	 * @throws SQLException 
	 */
	public List<AuthGroup> queryGroupIdByState(String state) throws SQLException{
		String sql = "select p_id as pid from t_auth_group where s_state = '"+state+"'";
		return dbHelper.getBeanList(sql, AuthGroup.class);
		
	}
	/**
	 * 根据组id查询专家id
	 * @throws SQLException 
	 */
	public List<AuthExportGroupMap> queryExpertIdByGroupId(String groupId) throws SQLException{
		String sql = "select s_export_id as exportId from t_auth_export_group_map where s_group_id = ?";
		return dbHelper.getBeanList(sql, AuthExportGroupMap.class, groupId);
		
	}
	/**
	 * 根据现场考察专家id查询 现场考察专家身份
	 * @param expertId
	 * @return
	 * @throws SQLException 
	 */
	public AuthExportGroupMap queryAuthExportGroupMapByExpertId2(String state,String expertId) throws SQLException{
		String sql="SELECT TOP 1 a.p_id as pid ,a.s_type as type ," +
				" a.s_identity as identitys,a.s_export_id as exportId ," +
				" a.s_group_id as groupId ,a.s_create_time as createTime  " +
				" FROM t_auth_export_group_map as a " +
				" LEFT JOIN t_auth_group as b ON a.s_group_id=b.p_id  " +
				" LEFT JOIN t_auth_school as c ON b.s_school_id=c.p_id " +
				" WHERE  b.s_type = '1' AND a.s_export_id= ? ";
		sql += " and b.s_state = '"+state+"'";
		AuthExportGroupMap a=dbHelper.getBean(sql, AuthExportGroupMap.class,expertId);
		return a;
		
	}
	/**
	 * 查询组Id
	 * @throws SQLException 
	 */
	public AuthExportGroupMap queryGroupId(String expertId) throws SQLException{
		String sql = "select a.s_group_id as groupId from t_auth_export_group_map as a left join t_auth_group as b on a.s_group_id = b.p_id where b.s_state != 3 and a.s_type = 0 and a.s_export_id = ?";
		return dbHelper.getBean(sql, AuthExportGroupMap.class, expertId);
		
	}
	
	/**
	 * 删除(学校与组的关系材料汇总)
	 */
	public void deleteAuthSchoolGroupMap(String schoolId, String groupId) throws SQLException{
		String sql="DELETE FROM t_auth_school_group_map WHERE s_school_id =? and s_group_id = ?";
		dbHelper.execute(sql, schoolId, groupId);
	}
	
	/**
	 * 填报数据查询(绑定)
	 */
	public Page<AuthSchool> queryAuthObservationSchool(String expertId ,Map<String, String> map) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT TOP 1000 c.p_id as pid ,c.s_major as major,c.s_school_name as schoolName,c.s_account as account from t_auth_export_group_map as a LEFT JOIN t_auth_group as b on a.s_group_id = b.p_id " +
				"LEFT JOIN t_auth_school as c on b.s_school_id = c.p_id where a.s_type = 1 AND b.s_type = 1 and b.s_state != 3 and a.s_export_id = '"+expertId+"'");
		if(StringUtils.isNotBlank(map.get("major"))){
			sql.append(" AND c.s_major = '"+map.get("major")+"'");
		}
		Page<AuthSchool> page = dbHelper.getPage(sql.toString(),AuthSchool.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return page;
	}
	
	/**
	 * 查询学校Id
	 */
	public AuthSchool querySchoolIdByExpertId(String expertId) throws SQLException{
		String sql = "SELECT c.p_id as pid ,c.s_major as major,c.s_school_name as schoolName,c.s_account as account,a.s_group_id as groupId,d.s_isfollow as isfollow from t_auth_export_group_map as a LEFT JOIN t_auth_group as b on a.s_group_id = b.p_id " +
				"LEFT JOIN t_auth_school as c on b.s_school_id = c.p_id left join t_auth_school_group_map as d on c.p_id = d.s_school_id where a.s_type = 1 AND b.s_type = 1 and b.s_state != 3 and a.s_export_id = '"+expertId+"'";
		return dbHelper.getBean(sql, AuthSchool.class);
	}
	
	/**
	 * 根据学校id查询该校材料评审结果
	 */
	public List<AuthItem> queryAuthmaterialResultBySchoolId(String schoolId) throws SQLException{
		String sql = "Select b.p_id, b.s_content as content, b.s_number as number,a.s_state as state , c.p_id as expertId, c.s_expert_name as expertName from t_auth_item_expert_map as a " +
				"left join t_auth_item as b on a.s_item_id = b.p_id left join t_auth_expert as c on a.s_expert_id = c.p_id " +
				" where a.s_school_id =? order by b.s_number";
		return dbHelper.getBeanList(sql, AuthItem.class, schoolId);
		
	}
	/**
	 * 查询组id根据专家
	 * @throws SQLException 
	 */
	public AuthExportGroupMap querygroupIdByExpertId(String expertId) throws SQLException{
		String sql = "select a.s_group_id as groupId from t_auth_export_group_map as a left join t_auth_group as b on a.s_group_id = b.p_id where a.s_type = 1 and b.s_state != 3 and a.s_export_id = ?";
		return dbHelper.getBean(sql, AuthExportGroupMap.class, expertId);
	}
	
	/**
	 * 查询专家id根据组id
	 */
	public List<AuthExportGroupMap> queryAuthExportGroupExportIdByGroupId(String groupId) throws SQLException{
		String sql = "select a.s_export_id as exportId"
				+ " from t_auth_export_group_map as a where a.s_group_id = ?";
		List<AuthExportGroupMap> list = dbHelper.getBeanList(sql, AuthExportGroupMap.class,groupId);
		return list;
	}
}
